from fastapi import FastAPI, HTTPException
from pydantic import BaseModel
from typing import Optional
from datetime import datetime
import mysql.connector
import logging
from fastapi.middleware.cors import CORSMiddleware


app = FastAPI()

# 添加 CORS 中间件
app.add_middleware(
    CORSMiddleware,
    allow_origins=["*"],  # 允许所有来源（在生产环境中应限制为特定域名）
    allow_credentials=True,
    allow_methods=["*"],  # 允许所有 HTTP 方法
    allow_headers=["*"],  # 允许所有请求头
)

# 配置日志
logging.basicConfig(level=logging.INFO)
logger = logging.getLogger(__name__)

# 数据库配置
db_config = {
    'user': 'root',
    'password': '123456',
    'host': '127.0.0.1',  #可能需要修改为真实的ip
    'database': 'esp32_record'
}

# 数据模型
class SensorRecord(BaseModel):
    id: Optional[int] = None
    sensor_name: str
    sensor_value: float
    create_time: Optional[str] = None
    update_time: Optional[str] = None

    class Config:
        from_attributes = True

# 创建数据库连接
def get_db_connection():
    conn = mysql.connector.connect(**db_config)
    return conn

# 增加数据
@app.post("/api/add_record")
def add_record(data: SensorRecord):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        sql = "INSERT INTO esp32_data (sensor_name, sensor_value) VALUES (%s, %s)"
        values = (data.sensor_name, data.sensor_value)
        
        logger.info(f"Executing SQL: {sql} with values {values}")
        
        cursor.execute(sql, values)
        conn.commit()
        
        cursor.close()
        conn.close()
        
        return {"code": 200, "msg": "添加成功"}
    
    except Exception as e:
        logger.error(f"Error adding record: {str(e)}")
        raise HTTPException(status_code=404, detail="添加失败")

# 查询数据
@app.get("/api/get_records")
async def get_records(sensor_name: str, create_time: str):
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)  # 返回字典格式的结果
        
        # 使用参数化查询防止SQL注入
        sql = """
            SELECT * FROM esp32_data 
            WHERE sensor_name = %s 
            AND DATE(create_time) = %s
        """
        values = (sensor_name, create_time)
        
        logger.info(f"Executing SQL: {sql} with values {values}")
        
        cursor.execute(sql, values)
        results = cursor.fetchall()
        
        # 处理查询结果
        processed_results = []
        for record in results:
            processed_record = {
                **record,
                'create_time': handle_date(record['create_time']),
                'update_time': handle_date(record['update_time'])
            }
            processed_results.append(processed_record)
        
        logger.info(f"Found {len(processed_results)} records")
        
        cursor.close()
        conn.close()
        
        return {
            "code": 200,
            "msg": "查询成功",
            "data": processed_results
        }
    
    except Exception as e:
        logger.error(f"Error querying records: {str(e)}")
        raise HTTPException(status_code=404, detail="查询失败")

# 处理时间格式的函数
def handle_date(date_obj: datetime) -> str:
    if date_obj:
        return date_obj.strftime("%Y-%m-%d %H:%M:%S")
    return None

# 查询单条数据接口
@app.get("/api/get_record/{data_id}")
def read_data(data_id: int):
    try:
        conn = get_db_connection()
        cursor = conn.cursor(dictionary=True)  # 返回字典格式的结果
        
        sql = "SELECT * FROM esp32_data WHERE id = %s"
        values = (data_id,)
        
        logger.info(f"Executing SQL: {sql} with values {values}")
        
        cursor.execute(sql, values)
        result = cursor.fetchone()
        
        cursor.close()
        conn.close()
        
        if result:
            # 处理日期时间格式
            processed_result = {
                **result,
                'create_time': handle_date(result['create_time']),
                'update_time': handle_date(result['update_time'])
            }
            
            return {
                "code": 200,
                "msg": "查询成功",
                "data": processed_result
            }
        else:
            raise HTTPException(status_code=404, detail="数据未找到")
            
    except Exception as e:
        logger.error(f"Error querying record: {str(e)}")
        raise HTTPException(status_code=404, detail="查询失败")

# 更新数据
@app.put("/api/update_record/{data_id}")
def update_data(data_id: int, data: SensorRecord):
    try:
        conn = get_db_connection()
        cursor = conn.cursor()
        
        sql = "UPDATE esp32_data SET sensor_name = %s, sensor_value = %s WHERE id = %s"
        values = (data.sensor_name, data.sensor_value, data_id)
        
        logger.info(f"Executing SQL: {sql} with values {values}")
        
        cursor.execute(sql, values)
        conn.commit()
        
        cursor.close()
        conn.close()
        
        return {
            "code": 200,
            "msg": "更新成功"
        }
        
    except Exception as e:
        logger.error(f"Error updating record: {str(e)}")
        raise HTTPException(status_code=404, detail="更新失败")

# 删除数据
@app.delete("/api/delete_record/{data_id}")
def delete_data(data_id: int):
    conn = get_db_connection()
    cursor = conn.cursor()
    cursor.execute("DELETE FROM esp32_data WHERE id = %s", (data_id,))
    conn.commit()
    cursor.close()
    conn.close()
    return {"message": "数据删除成功"}
